package models

import (
	"database/sql"
	"fmt"
	"log"
	"time"
)

// Order 结构体表示订单
type Order struct {
	ID          int         `json:"id"`             // 订单ID
	UserID      int         `json:"user_id"`        // 用户ID
	OrderDate   time.Time   `json:"order_date"`     // 订单日期
	Status      string      `json:"status"`         // 订单状态
}

// OrderItem 结构体表示订单中的商品
type OrderItem struct {
	OrderID     int         `json:"order_id"`       // 订单ID
	ProductID   int         `json:"product_id"`     // 商品ID
	Quantity    int         `json:"quantity"`       // 商品数量
	Price       float64     `json:"price"`          // 商品单价
	Status      string      `json:"status"`         // 商品状态
	ProductName string      `json:"productname"`    // 商品名称
	ImageURL    string      `json:"image_url"`      // 商品图片URL
	IsCommented bool        `json:"is_commented"`   // 是否已评论
}

// CreateOrder 创建新订单
func CreateOrder(userID int) (int64, error) {
	stmt, err := db.Prepare(`
		INSERT INTO orders(user_id, order_date, status) 
		VALUES(?, CURRENT_TIMESTAMP, '未支付')`)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(userID)
	if err != nil {
		return 0, err
	}

	return result.LastInsertId()
}

// 向订单中添加商品
func AddToOrder(userID int, productID int, quantity int) (int64, error) {
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()

	// 检查用户是否存在
	_, err = GetUserByID(userID)
	if err != nil {
		return 0, fmt.Errorf("用户不存在")
	}

	// 检查商品是否存在
	product, err := GetProductByID(productID)
	if err != nil {
		return 0, err
	}
	if product.ID == 0 {
		return 0, fmt.Errorf("商品不存在")
	}

	// 创建订单
	orderID, err := CreateOrder(userID)
	if err != nil {
		return 0, err
	}

	// 添加商品到订单
	err = AddOrderItem(int(orderID), productID, quantity, product.Price)
	if err != nil {
		return 0, err
	}

	// 提交事务
	if err = tx.Commit(); err != nil {
		return 0, err
	}

	return orderID, nil
}

// AddOrderItem 添加商品到订单
func AddOrderItem(orderID int, productID int, quantity int, price float64) error {
	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("开启事务失败: %v", err)
	}
	// 确保在函数返回时要么提交要么回滚
	var committed bool
	defer func() {
		if !committed {
			tx.Rollback()
		}
	}()

	// 获取订单状态
	var orderStatus string
	err = tx.QueryRow("SELECT status FROM orders WHERE id = ?", orderID).Scan(&orderStatus)
	if err != nil {
		return fmt.Errorf("获取订单状态失败: %v", err)
	}

	// 验证商品是否存在
	var product Product
	err = tx.QueryRow("SELECT id, productname, price, quantity FROM products WHERE id = ?", productID).
		Scan(&product.ID, &product.ProductName, &product.Price, &product.Quantity)
	if err != nil {
		return fmt.Errorf("商品不存在: %v", err)
	}

	// 验证库存
	if product.Quantity < quantity {
		return fmt.Errorf("商品库存不足")
	}

	// 验证订单是否存在
	var exists bool
	err = tx.QueryRow("SELECT EXISTS(SELECT 1 FROM orders WHERE id = ?)", orderID).Scan(&exists)
	if err != nil {
		return fmt.Errorf("验证订单失败: %v", err)
	}
	if !exists {
		return fmt.Errorf("订单不存在")
	}

	// 使用事务执行插入，使用订单的状态
	_, err = tx.Exec(`
        INSERT INTO order_items(order_id, product_id, quantity, price, status) 
        VALUES(?, ?, ?, ?, ?)`,
		orderID, productID, quantity, price, orderStatus)
	if err != nil {
		return fmt.Errorf("执行SQL失败: %v", err)
	}

	// 更新商品库存
	_, err = tx.Exec("UPDATE products SET quantity = quantity - ? WHERE id = ?", quantity, productID)
	if err != nil {
		return fmt.Errorf("更新库存失败: %v", err)
	}

	// 提交事务
	err = tx.Commit()
	if err == nil {
		committed = true
	}
	if err != nil {
		return fmt.Errorf("提交事务失败: %v", err)
	}

	return nil
}

// GetOrderByUserID 根据用户ID获取订单信息以及订单中包含的商品
func GetOrderByUserID(userID int) (Order, []OrderItem, error) {
	var order Order
	// 获取最新的未支付订单
	err := db.QueryRow(`
        SELECT id, user_id, order_date, status 
        FROM orders 
        WHERE user_id = ? AND status = '未支付'
        ORDER BY order_date DESC LIMIT 1`,
		userID).Scan(&order.ID, &order.UserID, &order.OrderDate, &order.Status)
	if err != nil {
		if err == sql.ErrNoRows {
			return order, nil, nil
		}
		return order, nil, err
	}

	// 获取订单中的商品信息
	var orderItems []OrderItem
	rows, err := db.Query("SELECT order_id, product_id, quantity, price, status FROM order_items WHERE order_id = ?", order.ID)
	if err != nil {
		return order, nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var item OrderItem
		err := rows.Scan(&item.OrderID, &item.ProductID, &item.Quantity, &item.Price, &item.Status)
		if err != nil {
			return order, nil, err
		}
		orderItems = append(orderItems, item)
	}

	return order, orderItems, nil
}

// GetLatestOrder 获取用户最新的订单
func GetLatestOrder(userID int) (*Order, []OrderItem, error) {
	// 获取用户最新的订单
	var order Order
	err := db.QueryRow(`
        SELECT id, user_id, order_date, status 
        FROM orders 
        WHERE user_id = ? 
        ORDER BY order_date DESC 
        LIMIT 1`, userID).Scan(&order.ID, &order.UserID, &order.OrderDate, &order.Status)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil, fmt.Errorf("未找到")
		}
		return nil, nil, err
	}

	// 获取订单项
	rows, err := db.Query(`
        SELECT oi.order_id, oi.product_id, oi.quantity, oi.price, oi.status,
               p.productname, p.image_url,
               EXISTS(SELECT 1 FROM comments WHERE user_id = ? AND product_id = oi.product_id) as is_commented
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = ?`, userID, order.ID)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	var orderItems []OrderItem
	for rows.Next() {
		var item OrderItem
		err := rows.Scan(
			&item.OrderID, &item.ProductID, &item.Quantity,
			&item.Price, &item.Status, &item.ProductName,
			&item.ImageURL, &item.IsCommented)
		if err != nil {
			return nil, nil, err
		}
		orderItems = append(orderItems, item)
	}

	return &order, orderItems, nil
}

// 从订单中除商品
func DeleteFromOrder(userID int, productID int) (int64, error) {
	// 获取订单ID
	var orderID int
	err := db.QueryRow("SELECT user_id FROM orders WHERE user_id = ?", userID).Scan(&orderID)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Println("No unpaid order found for user ID:", userID)
			return 0, fmt.Errorf("未找到未支付的订单")
		}
		log.Println("Error getting order ID for DeleteFromOrder:", err)
		return 0, err
	}

	// 删除订单中的商品
	stmt, err := db.Prepare("DELETE FROM order_items WHERE order_id = ? AND product_id = ?")
	if err != nil {
		log.Println("Error preparing statement for DeleteFromOrder:", err)
		return 0, err
	}
	defer stmt.Close()

	result, err := stmt.Exec(orderID, productID)
	if err != nil {
		log.Println("Error executing DeleteFromOrder:", err)
		return 0, err
	}

	// 获取删除的行数
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Println("Error getting rows affected for DeleteFromOrder:", err)
		return 0, err
	}

	return rowsAffected, nil
}

// UpdateOrderStatus 更新订单状态
func UpdateOrderStatus(orderID int, status string) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// 更新订单状态
	_, err = tx.Exec("UPDATE orders SET order_date = ?, status = ? WHERE id = ?", time.Now(), status, orderID)
	if err != nil {
		return err
	}

	// 更新订单项状态
	_, err = tx.Exec("UPDATE order_items SET status = ? WHERE order_id = ?", status, orderID)
	if err != nil {
		return err
	}

	return tx.Commit()
}

// GetOrderByID 根据订单ID获取订单信息
func GetOrderByID(orderID int) (*Order, []OrderItem, error) {
	// 获取订单信息
	var order Order
	err := db.QueryRow(`
        SELECT id, user_id, order_date, status 
        FROM orders 
        WHERE id = ?`,
		orderID).Scan(&order.ID, &order.UserID, &order.OrderDate, &order.Status)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil, fmt.Errorf("订单不存在")
		}
		return nil, nil, err
	}

	// 获取订单项
	rows, err := db.Query(`
        SELECT oi.order_id, oi.product_id, oi.quantity, oi.price, oi.status,
               p.productname, p.image_url,
               EXISTS(SELECT 1 FROM comments c WHERE c.order_id = oi.order_id AND c.product_id = oi.product_id) as is_commented
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = ?`, orderID)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	var orderItems []OrderItem
	for rows.Next() {
		var item OrderItem
		err := rows.Scan(
			&item.OrderID, &item.ProductID, &item.Quantity,
			&item.Price, &item.Status, &item.ProductName,
			&item.ImageURL, &item.IsCommented)
		if err != nil {
			return nil, nil, err
		}
		orderItems = append(orderItems, item)
	}

	return &order, orderItems, nil
}

// GetAllOrders 获取用户的所有订单
func GetAllOrders(userID int) ([]Order, map[int][]OrderItem, error) {
	// 获取所有订单
	rows, err := db.Query(`
        SELECT id, user_id, order_date, status 
        FROM orders 
        WHERE user_id = ? 
        ORDER BY order_date DESC`, userID)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	var orders []Order
	for rows.Next() {
		var order Order
		err := rows.Scan(&order.ID, &order.UserID, &order.OrderDate, &order.Status)
		if err != nil {
			return nil, nil, err
		}
		orders = append(orders, order)
	}

	// 获取所有订单的商品信息
	orderItems := make(map[int][]OrderItem)
	for _, order := range orders {
		rows, err := db.Query(`
            SELECT oi.order_id, oi.product_id, oi.quantity, oi.price, oi.status,
                   p.productname, p.image_url,
                   EXISTS(
                       SELECT 1 
                       FROM comments c 
                       WHERE c.user_id = ? 
                       AND c.product_id = oi.product_id 
                       AND c.order_id = oi.order_id
                   ) as is_commented
            FROM order_items oi
            JOIN products p ON oi.product_id = p.id
            WHERE oi.order_id = ?`, userID, order.ID)
		if err != nil {
			return nil, nil, err
		}
		defer rows.Close()

		var items []OrderItem
		for rows.Next() {
			var item OrderItem
			err := rows.Scan(
				&item.OrderID, &item.ProductID, &item.Quantity,
				&item.Price, &item.Status, &item.ProductName,
				&item.ImageURL, &item.IsCommented)
			if err != nil {
				return nil, nil, err
			}
			items = append(items, item)
		}
		orderItems[order.ID] = items
	}

	return orders, orderItems, nil
}

// UpdateOrderItemCommentStatus 更新订单项的评论状态
func UpdateOrderItemCommentStatus(userID int, productID int, orderID int) error {
	// 更新订单项的评论状态
	_, err := db.Exec(`
        UPDATE order_items 
        SET is_commented = true 
        WHERE order_id = ? 
        AND product_id = ?`,
		orderID, productID)
	return err
}
